package com.cs157a.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 
 * @author Hiep
 */
public class MakeDao {

	private String table = "maker";

	public List loadAll(Connection conn) throws SQLException {

		String sql = "SELECT * FROM " + table + " ORDER BY MAK ASC ";
		List searchResults = listQuery(conn, conn.prepareStatement(sql));

		return searchResults;
	}

	public int countMaker(Connection conn) throws SQLException {
		String sql = "SELECT COUNT(*) as COUNT FROM " + table;
		ResultSet res = null;
		int count = 0;
		Statement stmnt = conn.createStatement();
		res = stmnt.executeQuery(sql);
		res.next();
		count = res.getInt("COUNT");
		stmnt.close();
		return count;

	}

	protected List listQuery(Connection conn, PreparedStatement stmt)
			throws SQLException {

		ArrayList searchResults = new ArrayList();
		ResultSet result = null;

		try {
			result = stmt.executeQuery();

			while (result.next()) {
				Make temp = new Make();

				temp.setName(result.getString("MAK"));
				temp.setCode(result.getString("COD"));

				searchResults.add(temp);
			}

		} finally {
			if (result != null) {
				result.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}

		return (List) searchResults;
	}

	public void create(Connection conn, Make maker) throws SQLException {
		String sql = "INSERT INTO " + table + " VALUES";
		sql += "(" + maker.getNo() + ",'" + maker.getName() + "','"
				+ maker.getCode() + "')";
		//Statement stmnt = conn.createStatement();
		//stmnt.executeQuery(sql);
		ArrayList<PreparedStatement> stmnt = new ArrayList<PreparedStatement>();
		stmnt.add(conn.prepareStatement(sql));
		
		sql = "CREATE TABLE "
				+ "APL"
				+ maker.getCode()
				+ " ( MODEL VARCHAR2(25) NOT NULL, "
				+ " YEAR VARCHAR2(4) NOT NULL, "
				+ " DESCRIPTION VARCHAR2(51), "
				+ " LITRES VARCHAR2(4), "
				+ " ENGINE_TYPE VARCHAR2(3), "
				+ " CUBIC_INCHES VARCHAR2(6), "
				+ " RLINK NUMBER(4), "
				+ " PRIMARY KEY (MODEL, YEAR, DESCRIPTION, LITRES, ENGINE_TYPE, CUBIC_INCHES, RLINK), "
				+ " FOREIGN KEY (RLINK) REFERENCES RADCRX)";

		//stmnt.executeQuery(sql);
		stmnt.add(conn.prepareStatement(sql));
		
		int rows = databaseUpdate(conn, stmnt);
		//stmnt.close();
	}


    /**
     * databaseUpdate-method. This method is a helper method for internal use. It will execute
     * all database handling that will change the information in tables. SELECT queries will
     * not be executed here however. The return value indicates how many rows were affected.
     * This method will also make sure that if cache is used, it will reset when data changes.
     *Transaction Added
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(Connection conn, ArrayList<PreparedStatement> stmt) throws SQLException {

          int result = -1;
    	  conn.setAutoCommit(false);// begin transaction 
          Savepoint savepoint = conn.setSavepoint("makeSavePoint");
          try {
        	  for(int i = 0; i < stmt.size(); i++) {
        		  result = stmt.get(i).executeUpdate();
        		  stmt.get(i).close();
        	  }
        	  conn.commit();//end transaction 
          }
          catch (Exception exc) {
        	  conn.rollback(savepoint);// cancel transaction, rollback to savepoint
        	  exc.printStackTrace();
          }
    	  conn.setAutoCommit(true);// exit transaction 
          return result;
    }


	public int getNextMakerNo(Connection conn) throws SQLException {
		String sql = "SELECT MNO FROM " + table + " ORDER BY MNO";
		ResultSet res = null;
		int count = 1;
		Statement stmnt = conn.createStatement();
		res = stmnt.executeQuery(sql);
		while (res.next()) {
			if (res.getInt("MNO") > count) {
				break;
			}
			count++;
		}
		stmnt.close();
		System.out.println(count);
		return count;
	}

	public List<String> getAllMakeCode(Connection connection) throws SQLException {
		String sql = "Select COD from MAKER ORDER by COD";
		List<String> list = new ArrayList<String>();
		PreparedStatement stmt = null;
		stmt = connection.prepareStatement(sql);
		ResultSet res = stmt.executeQuery();
		while (res.next()) {
			list.add(res.getString("COD"));
		}
		return list;
	}


	static public String getMakeCode(Connection connection, String maker) throws SQLException {
		String sql = "Select COD from MAKER where MAK = '"+maker+"'";
		String code = null;
		PreparedStatement stmt = null;
		stmt = connection.prepareStatement(sql);
		ResultSet res = stmt.executeQuery();
		while (res.next()) {
			code = res.getString("COD");
		}
		return code;
	}

	public String searchName(Connection connection, String code)
			throws SQLException {
		String sql = "Select MAK from MAKER where COD = '" + code + "'";
		String s = "";
		ResultSet res = null;
		Statement stmnt = connection.createStatement();
		res = stmnt.executeQuery(sql);
		res.next();
		s = res.getString("MAK");
		stmnt.close();
		return s;

	}
}
